import re
import pandas as pd
import numpy as np
from datetime import datetime, date, timedelta
from sqlalchemy import and_
from dateutil.relativedelta import relativedelta
from app.utils.common_func_defs import *
from app.models.data_base_models import *
from app.services import get_engine


from fuzzywuzzy import process
def fuzzy_match_dataframe(df, column_name, target_string):
    """
    对DataFrame中的某一列进行模糊匹配处理，返回相似性最高的行

    Parameters:
        df (pd.DataFrame): 要处理的DataFrame
        column_name (str): 要匹配的列名
        target_string (str): 目标字符串

    Returns:
        pd.DataFrame: 与目标字符串相似性最高的行组成的DataFrame
    """
    # 确保目标列存在于DataFrame中
    if column_name not in df.columns:
        raise ValueError(f"列 '{column_name}' 不存在于DataFrame中")

    # 使用process库来模糊匹配查询
    matches = process.extract(target_string, df[column_name], limit=None)

    # 筛选符合相似度阈值的结果
    filtered_matches = [(match[0], match[1]) for match in matches if match[1] >= threshold]

    # 创建包含匹配结果的新DataFrame
    result_df = df[df[column_name].isin([match[0] for match in filtered_matches])]

    return result_df


####################################################### 加工方法定义 ########################################################
'''—————————————————————————————方法：抖音旗舰店直播间日统计表（达播报表加工）—————————————————————————————'''
def dy_flagship_store_living_room_day_stat_exe(start_date, end_date, target_liver=''):
    engine = get_engine()

    df_dy_flagship_store_living_room_day = pd.read_sql_query(
        f"SELECT * FROM dy_flagship_store_living_room_day WHERE stat_time BETWEEN '{start_date}' AND '{end_date}'",
        engine)
    if df_dy_flagship_store_living_room_day.empty:
        return None

    df_dy_flagship_store_living_room_day.drop(['id', 'create_time', 'upload_time'], axis=1, inplace=True)

    df_69_info = pd.read_sql_query('SELECT * FROM good_indicator', engine)
    df_69_info.drop(['id', 'create_time'], axis=1, inplace=True)
    tp = df_dy_flagship_store_living_room_day.merge(df_69_info, on='sixty_nine_code', how='left')

    # sixty_nine_code
    output = tp.groupby(['stat_time', 'livestreamer_name'])['sixty_nine_code'].agg(lambda x: ';'.join(x.unique())).reset_index(
        name='sixty_nine_code')

    # living_duration
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name'])['living_duration'].agg(lambda x: int(x.sum())).reset_index(name='living_duration')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_times
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).size().reset_index(name='living_times')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    output['living_times'] = output['living_times'].apply(lambda x: int(x))
    del df_ttp

    # living_max_online_user_count
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name'])['living_max_online_user_count'].agg(lambda x: int(x.max())).reset_index(name='living_max_online_user_count')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_average_online_user_count
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name'])['living_average_online_user_count'].agg(lambda x: x.mean()).\
        reset_index(name='living_average_online_user_count')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    output['living_average_online_user_count'] = output['living_average_online_user_count'].apply(lambda x: int(x))
    del df_ttp

    # living_all_goods_view_to_trans_count_rate
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(
        lambda x: x['living_all_goods_trans_count'].sum()/ x['living_view_count'].sum()  ).reset_index(name='living_all_goods_view_to_trans_count_rate')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_trans_amount
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name'])['living_all_goods_trans_amount'].agg(lambda x: x.sum()).\
        reset_index(name='living_trans_amount')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_trans_count
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(lambda x: x['living_all_goods_trans_good_count'].sum()).\
        reset_index(name='living_trans_count')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_refund_amount
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(lambda x: x['living_all_goods_refund_amount'].sum()).\
        reset_index(name='living_refund_amount')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_actual_trans_amount
    output['living_actual_trans_amount'] = output['living_trans_amount'] - output['living_refund_amount']


    # living_initial_trans_amount
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(lambda x: (x['good_initial_price'] * x['living_all_goods_trans_count']).sum()).\
        reset_index(name='living_initial_trans_amount')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_service_fee
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(lambda x: x['living_service_fee'].sum()).\
        reset_index(name='living_service_fee')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_commission
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(lambda x: x['living_commission'].sum()).\
        reset_index(name='living_commission')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')
    del df_ttp

    # living_discount_rate
    output['living_discount_rate'] = (output['living_initial_trans_amount'] - output['living_actual_trans_amount']) / output['living_initial_trans_amount']

    # living_giveaway_item_price_ratio
    df_ttp = tp.groupby(['stat_time', 'livestreamer_name']).apply(lambda x:
    (x['good_giveaway_item_price_ratio'] * x['good_initial_price'] * x['living_all_goods_trans_count']).sum() / (x['good_initial_price'] * x['living_all_goods_trans_count']).sum()).\
        reset_index(name='living_giveaway_item_price_ratio')
    output = output.merge(df_ttp, on=['stat_time', 'livestreamer_name'], how='left')

    del df_ttp

    # living_fee_rate
    output['living_fee_rate'] = (output['living_service_fee'] + output['living_commission']) / output['living_initial_trans_amount'] + output['living_discount_rate'] + output['living_giveaway_item_price_ratio']

    # living_roi
    output['living_roi'] = 1 / output['living_discount_rate']

    # output = output.applymap(format_number)

    # 转中文字段名
    output = uploaded_field_corr_entozh_res(output, '抖音旗舰店直播间日统计表')
    output = output.fillna('nan')

    if target_liver == '':
        return output
    else:
        return fuzzy_match_dataframe(output, '达人', target_liver)


'''—————————————————————————————方法：淘宝客竞品推广日统计表（淘客报表加工）—————————————————————————————'''
def dtk_competing_brand_promotion_day_stat_exe(start_date, end_date):
    engine = get_engine()

    df_dtk_competing_brand_promotion_day = pd.read_sql_query(
        f"SELECT * FROM dtk_competing_brand_promotion_day WHERE stat_time BETWEEN '{start_date}' AND '{end_date}'",
        engine)

    if df_dtk_competing_brand_promotion_day.empty:
        return None
    df_dtk_competing_brand_promotion_day= df_dtk_competing_brand_promotion_day.drop(['id', 'crawl_time','create_time'], axis=1)

    df_dtk_competing_brand_promotion_day = df_dtk_competing_brand_promotion_day.applymap(format_number)

    # 转中文字段名
    df_dtk_competing_brand_promotion_day = uploaded_field_corr_entozh_res(df_dtk_competing_brand_promotion_day, '淘宝客竞品推广日统计表')
    df_dtk_competing_brand_promotion_day = df_dtk_competing_brand_promotion_day.fillna('nan')
    return df_dtk_competing_brand_promotion_day


'''—————————————————————————————方法：查价结果表1、2（查价加工）—————————————————————————————'''
def check_price_res1_exe(product_bar_code_and_product_num):
    engine = get_engine()

    import re
    pattern = r'^(\d+:\d+;)*(\d+:\d+)?$'
    if re.match(pattern, product_bar_code_and_product_num) is None:
        return None  # product_bar_code_and_product_num 字符串不合规范

    #     # 按分号分隔字符串
    substrings = product_bar_code_and_product_num.split(";")

    # 分隔每个子字符串并存储在两个列表中
    product_bar_code = [sub.split(":")[0] for sub in substrings]
    product_num = [int(sub.split(":")[1]) for sub in substrings]

    df_tp = pd.DataFrame(
        {
            'product_bar_code': product_bar_code,
            'product_pack_count': product_num}
    )
    df_tp['sixty_nine_code'] = 69
    df_tp['product_combination'] = product_bar_code_and_product_num

    ######################## 处理开单价 ########################
    # 连接表
    shangpin_jiage = pd.read_sql_query('SELECT * FROM good_product_price', engine)
    shangpin_jiage.drop(['id', 'upload_time', 'create_time', 'product_code', 'product_name'], axis=1, inplace=True)
    shangpin_jiage['product_bar_code'] = shangpin_jiage['product_bar_code'].str.replace(' ', '')

    df_type = pd.read_sql_query('SELECT * FROM good_product_code', engine)
    df_type.drop(['id', 'upload_time', 'create_time', 'product_code'], axis=1, inplace=True)

    shangpin_jiage = pd.merge(df_type, shangpin_jiage, on='product_bar_code', how='left')
    shangpin_jiage.fillna(0, inplace=True)  # 把type表中存在，但是价格体系表中不存在的值定为0
    shangpin_jiage['product_series'].replace(0, '', inplace=True)

    df_tp = pd.merge(df_tp, shangpin_jiage, on='product_bar_code', how='left')  # 此处建议还是让他们上传全的type表
    del shangpin_jiage, df_type

    ####################### 赠品处理 ########################

    zengpin_jiage = pd.read_sql_query('SELECT * FROM good_giveaway_item_price', engine)
    zengpin_jiage.drop(['id', 'upload_time', 'create_time'], axis=1, inplace=True)

    zengpin_jiage['giveaway'] = True

    df_tp = pd.merge(df_tp, zengpin_jiage, left_on='product_bar_code', right_on='giveaway_item_bar_code', how='left')
    del zengpin_jiage

    # 赠品 product_retail_price
    df_tp['good_giveaway_item_price'] = df_tp['product_pack_count'] * df_tp['giveaway_item_price_per_pack']
    df_giveaway_item_price = df_tp.groupby('sixty_nine_code').agg(
        good_giveaway_item_price=('good_giveaway_item_price', 'sum')).reset_index()
    df_tp = df_tp.drop("good_giveaway_item_price", axis=1)
    df_tp = pd.merge(df_tp, df_giveaway_item_price, on='sixty_nine_code', how='left')
    del df_giveaway_item_price

    # def check_na_rows(df):
    #     na_rows = df[df['product_initial_price'].isna() & df['giveaway_item_bar_code'].isna()]
    #     return [''.join(na_rows['product_bar_code']), len(na_rows)]
    #
    # na_bar_code = check_na_rows(df_tp)  # 第一个元素是缺失的bar code
    # if na_bar_code[1] == len(df_tp):
    #     return None  # 表明他们查询的 product_bar_code_and_product_num 内，按照条码全都是匹配不上的

    df_tp['giveaway'].fillna(False, inplace=True)
    try:
        # 选择giveaway取值为TRUE的行并保存到df_giveaway_item_combination中
        df_giveaway_item_combination = df_tp[df_tp['giveaway'] == True].copy()

        # 生成新变量giveaway_item_combination取值为变量pack_count(int)和type(str)的作为字符串粘贴后的值
        df_giveaway_item_combination['good_giveaway_item_combination'] = df_giveaway_item_combination['product_pack_count'].astype(str) + '*' + \
                                                                    df_giveaway_item_combination['product_code']
        df_giveaway_item_combination = df_giveaway_item_combination[['sixty_nine_code', 'good_giveaway_item_combination']]
        df_giveaway_item_combination = df_giveaway_item_combination.groupby('sixty_nine_code')['good_giveaway_item_combination'].agg(lambda x: '+'.join(x))
        df_tp = pd.merge(df_tp, df_giveaway_item_combination, on='sixty_nine_code', how='left')
        del df_giveaway_item_combination
    except:
        pass

    df_tp['product_total_pieces'] = df_tp['product_pack_pieces'] * df_tp['product_pack_count']
    df_tp['product_pack_giveaway_item_price'] = df_tp['giveaway_item_price_per_pack']
    df_tp['product_total_giveaway_item_price'] = df_tp['giveaway_item_price_per_pack'] * df_tp['product_pack_count']
    df_tp['product_total_initial_price'] = df_tp['product_initial_price'] * df_tp['product_pack_count']
    df_tp['product_total_retail_price'] = df_tp['product_retail_price'] * df_tp['product_pack_count']
    df_tp['product_total_small_promotion_price'] = df_tp['product_small_promotion_price'] * df_tp['product_pack_count']
    df_tp['product_total_big_promotion_price'] = df_tp['product_big_promotion_price'] * df_tp['product_pack_count']

    df_tp = df_tp[['product_bar_code',
                   'product_name',
                   'product_pack_count',
                   'product_pack_pieces',
                   'product_total_pieces',
                   'product_pack_giveaway_item_price',
                   'product_total_giveaway_item_price',
                   'product_initial_price',
                   'product_total_initial_price',
                   'product_retail_price',
                   'product_total_retail_price',
                   'product_small_promotion_price',
                   'product_total_small_promotion_price',
                   'product_big_promotion_price',
                   'product_total_big_promotion_price']]

    df_tp.replace(0, np.nan, inplace=True)

    return df_tp

def check_price_res2_exe(good_actual_price, product_bar_code_and_product_num):
    engine = get_engine()

    df_tp0 = check_price_res1_exe(product_bar_code_and_product_num=product_bar_code_and_product_num)
    if df_tp0 is None:
        return None

    df_tp0.replace(np.nan, 0, inplace=True)
    good_pack_count = sum(df_tp0['product_pack_count'])
    good_total_pieces = sum(df_tp0['product_total_pieces'])
    good_giveaway_item_price = sum(df_tp0['product_total_giveaway_item_price'])
    good_initial_price = sum(df_tp0['product_total_initial_price'])
    good_small_promotion_price = sum(df_tp0['product_total_small_promotion_price'])
    good_big_promotion_price = sum(df_tp0['product_total_big_promotion_price'])
    def divide(a, b):
        if b == 0:
            return None
        return a / b
    good_each_piece_price = divide(good_actual_price, good_total_pieces)
    good_discount_fee_rate = divide((good_initial_price - good_actual_price), good_initial_price)
    good_giveaway_item_price_ratio = divide(good_giveaway_item_price, good_initial_price)
    good_is_break_big_promotion = '是' if good_actual_price < good_big_promotion_price else '否'
    good_is_break_small_promotion = '是' if good_actual_price < good_small_promotion_price else '否'

    df_tp = pd.DataFrame({
        'good_actual_price': [good_actual_price],
        'good_pack_count': [good_pack_count],
        'good_total_pieces': [good_total_pieces],
        'good_giveaway_item_price': [good_giveaway_item_price],
        'good_initial_price': [good_initial_price],
        'good_small_promotion_price': [good_small_promotion_price],
        'good_big_promotion_price': [good_big_promotion_price],
        'good_each_piece_price': [good_each_piece_price],
        'good_discount_fee_rate': [good_discount_fee_rate],
        'good_giveaway_item_price_ratio': [good_giveaway_item_price_ratio],
        'good_is_break_big_promotion': [good_is_break_big_promotion],
        'good_is_break_small_promotion': [good_is_break_small_promotion],
    })
    df_tp.fillna('0')

    return df_tp


def check_price_exe(good_actual_price, product_bar_code_and_product_num):
    df1 = check_price_res1_exe(product_bar_code_and_product_num)
    if df1 is not None:
        df1 = df1.fillna('nan')
        df1 = df1.applymap(format_number)
        # 转中文字段名
        df1 = uploaded_field_corr_entozh_res(df1, '查价结果表1')
    else:
        df1 = pd.DataFrame()

    df2 = check_price_res2_exe(good_actual_price, product_bar_code_and_product_num)
    if df2 is not None:
        df2 = df2.fillna('nan')
        df2 = df2.applymap(format_number)
        # 转中文字段名
        df2 = uploaded_field_corr_entozh_res(df2, '查价结果表2')
    else:
        df2 = pd.DataFrame()
    return [df1, df2]
